/*
	EXEC SP_COST_VARIANCE_REPORT '03/31/2010', 1, 2010, 2, 2010
*/
ALTER PROC SP_COST_VARIANCE_REPORT
@ASOFDATE	VARCHAR(50),
@FROM_QUARTER_NO	INT,
@FROM_YEAR	INT,
@TO_QUARTER_NO	INT,
@TO_YEAR	INT
 
AS
BEGIN
	CREATE TABLE #COST_TABLE 
		( [LOT ID] INT,[PROCESSSTEP ID] INT, [LOT CATEGORY] VARCHAR(20),[WAFER QTY] INT, [QTY IN] INT,
		  [LOT COST] NUMERIC(18,4),[LOT CUMMULATIVE COST] NUMERIC(18,4) ,[PART NUMBER] VARCHAR(50),[BASE PRODUCT] VARCHAR(50),
		  [PROCESS] VARCHAR(50),[PROCESS TYPE NAME] VARCHAR(50), [PROCESS TYPE ID] INT,[VENDOR NAME] VARCHAR(50), [STD COST1] NUMERIC(18,4), [STD COST2] NUMERIC(18,4) )

 	DECLARE @SQLSTRING NVARCHAR(4000)
	DECLARE @TRANSITSQLSTRING NVARCHAR(4000)

	DECLARE @TEMP_VARCHAR VARCHAR(50)
	DECLARE @START_DATE DATETIME
	DECLARE @END_DATE DATETIME

	DECLARE @LOT_ID INT
	DECLARE @PATH_PROCESSSTEP_ID INT
	DECLARE @PS_STEP_ID 	BIGINT
	DECLARE @PRJ_COST NUMERIC(10,4)
	DECLARE @PRJ_YIELD FLOAT
	DECLARE @SORT_STD_COST NUMERIC(10,4)
	DECLARE @SORT_YIELD FLOAT
	DECLARE @FAB_PROCESSSTEP_ID INT
	DECLARE @GDW INT
	DECLARE @TOTAL_STD_COST NUMERIC(10,4)
	DECLARE @NDW INT
	DECLARE @CUMM_STD_COST NUMERIC(10,4)
	DECLARE @PS_TYPE_NAME VARCHAR(20)
	DECLARE @PS_TYPE		VARCHAR(50)

	IF @ASOFDATE IS NULL OR LTRIM(RTRIM(@ASOFDATE)) = '' OR @ASOFDATE = '0'
	BEGIN
		SET @SQLSTRING = N'INSERT INTO #COST_TABLE
		SELECT 
			LTH.LOT_ID,LTH.PROCESSSTEP_ID,LOT_CATEGORY, WAFER_QTY_IN, QTY_IN,
			LOT_COST, LOT_CUMMULATIVE_COST,PART_NUMBER,
			(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
				CASE 
					WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
					WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
				END 
			) AS BASE_PRODUCT, PS_TYPE, PS_TYPE_NAME,
			(SELECT SORTING_ORDER FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = PS.PS_TYPE) AS PS_TYPE_ID,L_VENDOR_NAME, 
			UVCT1.Q'+CONVERT(VARCHAR,@FROM_QUARTER_NO)+'_STANDARD_COST, UVCT2.Q'+CONVERT(VARCHAR,@TO_QUARTER_NO)+'_STANDARD_COST
		FROM 
			LOT_TABLE LTH,LOCATION L, PRODUCT P,PROCESS_STEP PS 
		LEFT OUTER JOIN UPDATE_VIEW_COST_TABLE UVCT1 ON PS.PROCESSSTEP_ID = UVCT1.PROCESSSTEP_ID AND YEAR(UVCT1.fin_year) = ' + CONVERT(VARCHAR,@FROM_YEAR) +'
		LEFT OUTER JOIN UPDATE_VIEW_COST_TABLE UVCT2 ON PS.PROCESSSTEP_ID = UVCT2.PROCESSSTEP_ID AND YEAR(UVCT2.fin_year) = ' + CONVERT(VARCHAR,@TO_YEAR) +' 
		WHERE
			LTH.PROCESSSTEP_ID = P.PROCESSSTEP_ID
			AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
			AND LTH.LOCATION_ID = L.LOCATION_ID
			AND LTH.Lot_Category = ''PROD''
			AND P.P_TYPE IS NULL
			AND ( LOT_CURRENT_STATUS = ''RUNNING'' OR LOT_CURRENT_STATUS = ''HOLD'' ) '
	END
	ELSE
	BEGIN
		SET @TEMP_VARCHAR = CONVERT(VARCHAR, @ASOFDATE, 101)+' 12:00AM'
		SET @START_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)
		SET @TEMP_VARCHAR = NULL
		SET @TEMP_VARCHAR = CONVERT(VARCHAR, @ASOFDATE, 101)+' 11:59PM'
		SET @END_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)

		SET @SQLSTRING = N'INSERT INTO #COST_TABLE
		SELECT 
			LTH.LOT_ID,LTH.PROCESSSTEP_ID,LTH.LOT_CATEGORY,LTH.WAFER_QTY_IN , LTH.QTY_IN,
			ISNULL(LTH.LOT_COST,LT.LOT_COST ), ISNULL(LTH.LOT_CUMMULATIVE_COST,LT.LOT_CUMMULATIVE_COST ) , PART_NUMBER,
			(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
				CASE 
					WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
					WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
				END 
			) AS BASE_PRODUCT, PS_TYPE,PS_TYPE_NAME,
			(SELECT SORTING_ORDER FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = PS.PS_TYPE) AS PS_TYPE_ID, L_VENDOR_NAME, 
			UVCT1.Q'+CONVERT(VARCHAR,@FROM_QUARTER_NO)+'_STANDARD_COST, UVCT2.Q'+CONVERT(VARCHAR,@TO_QUARTER_NO)+'_STANDARD_COST
		FROM 
			LOT_TABLE_HISTORY LTH, LOT_TABLE LT,PRODUCT P, LOCATION L, PROCESS_STEP PS
			LEFT OUTER JOIN UPDATE_VIEW_COST_TABLE UVCT1 ON PS.PROCESSSTEP_ID = UVCT1.PROCESSSTEP_ID AND YEAR(UVCT1.fin_year) = ' + CONVERT(VARCHAR,@FROM_YEAR) +'
			LEFT OUTER JOIN UPDATE_VIEW_COST_TABLE UVCT2 ON PS.PROCESSSTEP_ID = UVCT2.PROCESSSTEP_ID AND YEAR(UVCT2.fin_year) = ' + CONVERT(VARCHAR,@TO_YEAR) +' 

		WHERE
			LTH.LOT_ID = LT.LOT_ID
			AND LTH.PROCESSSTEP_ID = P.PROCESSSTEP_ID
			AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
			AND LTH.LOCATION_ID = L.LOCATION_ID
			AND P.P_TYPE IS NULL
			AND LTH.Lot_Category = ''PROD''
			AND ( LTH.LOT_CURRENT_STATUS = ''RUNNING'' OR LTH.LOT_CURRENT_STATUS = ''HOLD'' )
			AND LTH.TIMESTAMP BETWEEN '''+ CONVERT(VARCHAR,@START_DATE)+''''+' AND '''+ CONVERT(VARCHAR,@END_DATE)+''''
	END 		
		
	PRINT '@SQLSTRING = '+@SQLSTRING
	EXEC (@SQLSTRING)

	
	IF @ASOFDATE IS NULL OR LTRIM(RTRIM(@ASOFDATE)) = '' OR @ASOFDATE = '0'
	BEGIN
		SET @TRANSITSQLSTRING = N'INSERT INTO #COST_TABLE
		SELECT 
			LTH.LOT_ID,LTH.PROCESSSTEP_ID,LOT_CATEGORY, CASE WHEN PS.PS_TYPE_NAME = ''FAB'' OR PS.PS_TYPE_NAME = ''WAFER STORE'' OR PS_TYPE_NAME = ''SORT'' THEN SHIP_Qty ELSE 0 END, 
			CASE WHEN PS.PS_TYPE_NAME <> ''FAB'' AND PS.PS_TYPE_NAME <> ''WAFER STORE'' AND PS_TYPE_NAME <> ''SORT'' THEN SHIP_Qty ELSE 0 END,
			SL.LOT_COST, SL.LOT_CUMMULATIVE_COST,PART_NUMBER,
			(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
				CASE 
					WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
					WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
				END 
			) AS BASE_PRODUCT, PS_TYPE, PS_TYPE_NAME,
			(SELECT SORTING_ORDER FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = PS.PS_TYPE) AS PS_TYPE_ID,L_VENDOR_NAME, 
			UVCT1.Q'+CONVERT(VARCHAR,@FROM_QUARTER_NO)+'_STANDARD_COST, UVCT2.Q'+CONVERT(VARCHAR,@TO_QUARTER_NO)+'_STANDARD_COST
		FROM 
			LOT_TABLE LTH,LOCATION L, Shipped_lot SL, PRODUCT P,PROCESS_STEP PS 
		LEFT OUTER JOIN UPDATE_VIEW_COST_TABLE UVCT1 ON PS.PROCESSSTEP_ID = UVCT1.PROCESSSTEP_ID AND YEAR(UVCT1.fin_year) = ' + CONVERT(VARCHAR,@FROM_YEAR) +'
		LEFT OUTER JOIN UPDATE_VIEW_COST_TABLE UVCT2 ON PS.PROCESSSTEP_ID = UVCT2.PROCESSSTEP_ID AND YEAR(UVCT2.fin_year) = ' + CONVERT(VARCHAR,@TO_YEAR) +' 
		WHERE
			LTH.PROCESSSTEP_ID = P.PROCESSSTEP_ID
			AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
			AND LTH.Lot_ID = SL.Lot_ID
			AND LTH.LOCATION_ID = L.LOCATION_ID
			AND P.P_TYPE IS NULL
			AND LTH.Lot_Category = ''PROD''
			AND (SL.Status = ''TRANSIT'') AND LTH.Lot_Category = ''PROD'''
	END
	ELSE
	BEGIN
		SET @TEMP_VARCHAR = CONVERT(VARCHAR, @ASOFDATE, 101)+' 12:00AM'
		SET @START_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)
		SET @TEMP_VARCHAR = NULL
		SET @TEMP_VARCHAR = CONVERT(VARCHAR, @ASOFDATE, 101)+' 11:59PM'
		SET @END_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)

		SET @TRANSITSQLSTRING = N'INSERT INTO #COST_TABLE
		SELECT 
			LTH.LOT_ID,LTH.PROCESSSTEP_ID,LTH.LOT_CATEGORY,CASE WHEN PS.PS_TYPE_NAME = ''FAB'' OR PS.PS_TYPE_NAME = ''WAFER STORE'' OR PS_TYPE_NAME = ''SORT'' THEN SHIP_Qty ELSE NULL END, 
			CASE WHEN PS.PS_TYPE_NAME <> ''FAB'' AND PS.PS_TYPE_NAME <> ''WAFER STORE'' AND PS_TYPE_NAME <> ''SORT'' THEN SHIP_Qty ELSE NULL END,
			SL.LOT_COST, SL.LOT_CUMMULATIVE_COST, PART_NUMBER,
			(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
				CASE 
					WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
					WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
				END 
			) AS BASE_PRODUCT, PS_TYPE,PS_TYPE_NAME,
			(SELECT SORTING_ORDER FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = PS.PS_TYPE) AS PS_TYPE_ID, L_VENDOR_NAME, 
			UVCT1.Q'+CONVERT(VARCHAR,@FROM_QUARTER_NO)+'_STANDARD_COST, UVCT2.Q'+CONVERT(VARCHAR,@TO_QUARTER_NO)+'_STANDARD_COST
		FROM 
			LOT_TABLE LTH, Shipped_lot_HISTORY SL,PRODUCT P,  LOCATION L, PROCESS_STEP PS
			LEFT OUTER JOIN UPDATE_VIEW_COST_TABLE UVCT1 ON PS.PROCESSSTEP_ID = UVCT1.PROCESSSTEP_ID AND YEAR(UVCT1.fin_year) = ' + CONVERT(VARCHAR,@FROM_YEAR) +'
			LEFT OUTER JOIN UPDATE_VIEW_COST_TABLE UVCT2 ON PS.PROCESSSTEP_ID = UVCT2.PROCESSSTEP_ID AND YEAR(UVCT2.fin_year) = ' + CONVERT(VARCHAR,@TO_YEAR) +' 

		WHERE
			LTH.PROCESSSTEP_ID = P.PROCESSSTEP_ID
			AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
			AND LTH.LOCATION_ID = L.LOCATION_ID
			AND LTH.Lot_ID = SL.Lot_ID
			AND P.P_TYPE IS NULL
			AND ( SL.STATUS = ''TRANSIT'' )
			AND LTH.Lot_Category = ''PROD''
			AND SL.TIMESTAMP BETWEEN '''+ CONVERT(VARCHAR,@START_DATE)+''''+' AND '''+ CONVERT(VARCHAR,@END_DATE)+''''
	END 		

	PRINT '@TRANSITSQLSTRING = '+@TRANSITSQLSTRING
	EXEC (@TRANSITSQLSTRING)

	-- DELETE THE FAB WAFER COST FROM THE REPORT.
	DELETE FROM #COST_TABLE WHERE [PROCESSSTEP ID] IN ( SELECT ProcessStep_ID FROM PROCESS_STEP WHERE PS_TYPE = 'FAB' AND ProcessStep_ID NOT IN ( SELECT ProcessStep_ID FROM Process_Parameter WHERE Parameter_key = 'IS_MEMS_WAFER' AND Parameter_Value = 'TRUE' ) )
	
	DECLARE @tmp VARCHAR(1000)
	DECLARE @STD_COST1_LABEL VARCHAR(100), @STD_COST2_LABEL VARCHAR(100), @STD_COST1_TOTAL_LABEL VARCHAR(100), @STD_COST2_TOTAL_LABEL VARCHAR(100)
	SET @STD_COST1_LABEL = 'Q'+CONVERT(VARCHAR,@FROM_QUARTER_NO)+'-'+CONVERT(VARCHAR,@FROM_YEAR)+' STD COST'
	SET @STD_COST2_LABEL = 'Q'+CONVERT(VARCHAR,@TO_QUARTER_NO)+'-'+CONVERT(VARCHAR,@TO_YEAR)+' STD COST'
	SET @STD_COST1_TOTAL_LABEL = 'Q'+CONVERT(VARCHAR,@FROM_QUARTER_NO)+'-'+CONVERT(VARCHAR,@FROM_YEAR)+' TOTAL COST'
	SET @STD_COST2_TOTAL_LABEL = 'Q'+CONVERT(VARCHAR,@TO_QUARTER_NO)+'-'+CONVERT(VARCHAR,@TO_YEAR)+' TOTAL COST'

	SET @tmp = N'SELECT *, 
					ISNULL([DIE QTY],[WAFER QTY])*ISNULL(['+@STD_COST1_LABEL+'],0) AS ['+@STD_COST1_TOTAL_LABEL+'],
					ISNULL([DIE QTY],[WAFER QTY])*ISNULL(['+@STD_COST2_LABEL+'],0) AS ['+@STD_COST2_TOTAL_LABEL+'],
					ISNULL([DIE QTY],[WAFER QTY])*ISNULL(['+@STD_COST2_LABEL+'],0) - ISNULL([DIE QTY],[WAFER QTY])*ISNULL(['+@STD_COST1_LABEL+'],0) AS [VARIANCE]
					
				 FROM 
				(SELECT  [BASE PRODUCT],[PART NUMBER],[PROCESS],SUM([WAFER QTY]) AS [WAFER QTY],SUM([QTY IN]) AS [DIE QTY],
			[STD COST1] AS [' + @STD_COST1_LABEL +'],
			[STD COST2]	AS [' + @STD_COST2_LABEL +']
			--ISNULL([DIE QTY],[WAFER QTY])*ISNULL([STD COST1],0) AS ['+@STD_COST1_TOTAL_LABEL+']
			--ISNULL(SUM([QTY IN]),SUM([WAFER QTY]))*ISNULL([STD COST2],0) AS ['+@STD_COST2_TOTAL_LABEL+']
			FROM #COST_TABLE 
			GROUP BY [BASE PRODUCT],[PROCESS TYPE ID],[PART NUMBER],[PROCESS], [STD COST1], [STD COST2]) AS XYZ'

	EXEC (@tmp)
END
